package cn.com.shopec.erp.purchase.dao.impl;


import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import cn.com.shopec.erp.common.orm.impl.HibernateEntityDao;
import cn.com.shopec.erp.common.page.PageFinder;
import cn.com.shopec.erp.common.page.PageQuery;
import cn.com.shopec.erp.common.utils.DateUtil;
import cn.com.shopec.erp.common.utils.LogicUtil;
import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.purchase.dao.IPurchaseRequisitionProductDao;
import cn.com.shopec.erp.purchase.entity.PurchaseRequisitionProduct;
import cn.com.shopec.erp.purchase.entity.PurchaseReturnOrder;
import cn.com.shopec.erp.purchase.vo.PurchaseRequisitionProductVo;
import cn.com.shopec.erp.stock.vo.StockInProductVo;
import cn.com.shopec.erp.stock.vo.StockInVo;

/**
 * @ClassName: PurchaseRequisitionProductDao
 * @Description: 请购单商品DAO接口实现
 * @author: gaoyaxiong
 * @date: 2013-5-21 下午18:14:13
 */
@Repository
public class PurchaseRequisitionProductDao extends HibernateEntityDao<PurchaseRequisitionProduct> implements IPurchaseRequisitionProductDao {

	
	/**
     * 
     * @Title: updateBrand
     * @Description: 修改次品信息
     * @param brandVo
     * @return
     * @throws
     * @author: duhui
     * @date: 2013-5-26下午04:17:40
     */
	@Override
	public boolean updatePurchaseOrder(PurchaseRequisitionProductVo pur) {
		boolean flag=false;
		try {
				StringBuffer sb=new StringBuffer();
				sb.append("update PurchaseRequisitionProduct set ");
				if(LogicUtil.isNotNull(pur.getSalePrice())){
					sb.append(" PP_UNIT_PRICE="+pur.getPpUnitPrice());
				}
				if(LogicUtil.isNotNull(pur.getTotalPrice())){
					sb.append(", TOTAL_PRICE="+pur.getTotalPrice());
				}
				if(LogicUtil.isNotNull(pur.getPpNumber())){
					sb.append(", PP_NUMBER="+pur.getPpNumber());
				}
				sb.append(" where id=\'"+pur.getId()+"\'");
				getSession().createQuery(sb.toString()).executeUpdate();
			    flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	

	/**
     * 
     * @Title: queryPurchaseList
     * @Description:采购单退货新增信息
     * @param brandVo
     * @return
     * @throws
     * @author: lsw
     * @date: 2013-5-26下午04:17:40
     */
	@SuppressWarnings("unchecked")
	@Override
	public List<PurchaseRequisitionProductVo> queryPurchaseList(Integer status ,Integer pageCount, Integer pageSize) {
		/*List<PurchaseRequisitionProductVo> purchase = null;*/
		List<PurchaseRequisitionProductVo> purchasList = new ArrayList<PurchaseRequisitionProductVo>();
		if(status!=null){
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT "
					+ "t.ID as id,"
					+ "t.PO_NO as poNo,"
					+ "t.PP_NAME as ppName,"
					+ "t.PP_PRODUCT_NO as productNo,"
					+ "t.PP_SPECIFICATION as ppSpecification,"
					+ "t.PP_UNIT as ppUnit,"
					+ "t.PP_NUMBER as ppNumber,"
					+ "t.PP_SUPPLIER_NO as ppSupplierNo,"
					+ "t.PP_SUPPLIER_NAME as ppSupplierName,"
					+ "t.PP_WAREHOUSE_NO as ppWarehouseNo,"
					+ "t.PP_WAREHOUSE_NAME as ppWarehouseName,"
					+ "t.PP_UNIT_PRICE as ppUnitPrice,"
					+ "t.DELETE_FLAG as deleteFlag,"
					+ "t.TOTAL_PRICE as totalPrice,"
					+ "t.SALE_PRICE as salePrice,"
					+ "t.PRODBAR_CODE as prodBarCode,"
					+ "t.COMMODITY_NO as commodityNo,"
					+ "e.yieldAmountNumner as yieldAmountNumner,"
					+ "e.defectiveAmountNumber as defectiveAmountNumber,"
					+ "e.commonNumner as commonNumner,"
					+ "e.remainingAmount as remainingAmount,"
					+ "q.QUALIFIED_QUANTITY as qualifiedQuantity,"
					+ "q.PRE_OCCUPY_QUANTITY as preOccupyQuantity "
					+ " from TBL_REQUISITION_PRODUCT t  "
					+ " join TBL_PURCHASE_ORDER r on t.PO_NO = r.PO_NO "
					+ " join TBL_STOCK_PURCHASE e on t.PO_NO = e.poNo "
					+ " JOIN TBL_WAREHOUSE se ON t.PP_WAREHOUSE_NO = se.WH_NO "
					+ " JOIN TBL_PRODUCT_STOCK q on e.STOCKNo = q.ID "
					+ "where t.DELETE_FLAG = 1 and t.RETURN_STATUS = 0 and r.po_status = "+status+""
					+ " AND se.WH_TYPE = 1 AND e.yieldAmountNumner > 0 and t.PP_PRODUCT_NO = e.prodNo ORDER BY r.CREATE_TIME DESC ");
					/*+ " AND se.WH_TYPE = 1 AND e.yieldAmountNumner > 0 GROUP BY t.PP_PRODUCT_NO ORDER BY r.CREATE_TIME DESC ");*/
			Query query= getSession().createSQLQuery(sql.toString())
					.addScalar("id",StandardBasicTypes.STRING)
					.addScalar("poNo",StandardBasicTypes.STRING)
					.addScalar("ppName",StandardBasicTypes.STRING)
					.addScalar("productNo",StandardBasicTypes.STRING)
					.addScalar("ppSpecification",StandardBasicTypes.STRING)
					.addScalar("ppUnit",StandardBasicTypes.STRING)
					.addScalar("ppNumber",StandardBasicTypes.INTEGER)
					.addScalar("ppSupplierNo",StandardBasicTypes.STRING)
					.addScalar("ppSupplierName",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseNo",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseName",StandardBasicTypes.STRING)
					.addScalar("ppUnitPrice",StandardBasicTypes.STRING)
					.addScalar("deleteFlag",StandardBasicTypes.INTEGER)
					.addScalar("totalPrice",StandardBasicTypes.STRING)
					.addScalar("salePrice",StandardBasicTypes.STRING)
					.addScalar("prodBarCode",StandardBasicTypes.STRING)
					.addScalar("commodityNo",StandardBasicTypes.STRING)
					.addScalar("commonNumner",StandardBasicTypes.LONG)
					.addScalar("remainingAmount",StandardBasicTypes.LONG)
					.addScalar("yieldAmountNumner",StandardBasicTypes.LONG)
					.addScalar("defectiveAmountNumber",StandardBasicTypes.LONG)
					.addScalar("qualifiedQuantity",StandardBasicTypes.LONG)
					.addScalar("preOccupyQuantity",StandardBasicTypes.LONG);
			
			if (pageCount != null && pageSize != null) {
				query.setFirstResult(pageCount).setMaxResults(pageSize);
			}
			query.setResultTransformer(Transformers.aliasToBean(PurchaseRequisitionProductVo.class));
			List<PurchaseRequisitionProductVo> purchase = query.list();
			for (PurchaseRequisitionProductVo element : purchase) {
				if( (element.getCommonNumner() !=0 && element.getRemainingAmount()!=0)){
					purchasList.add(element);
				}
			}
			
		}
		return purchasList;
	}



	@SuppressWarnings("unchecked")
	@Override
	public List<PurchaseRequisitionProduct> queryPoNoPurchaseList(PurchaseReturnOrder purchase) {
		if(purchase!=null){
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT "
					+ "t.ID as id,"
					+ "t.PO_NO as poNo,"
					+ "t.PP_NAME as ppName,"
					+ "t.PP_PRODUCT_NO as productNo,"
					+ "t.PP_SPECIFICATION as ppSpecification,"
					+ "t.PP_UNIT as ppUnit,"
					+ "t.PP_NUMBER as ppNumber,"
					+ "t.PP_SUPPLIER_NO as ppSupplierNo,"
					+ "t.PP_SUPPLIER_NAME as ppSupplierName,"
					+ "t.PP_WAREHOUSE_NO as ppWarehouseNo,"
					+ "t.PP_WAREHOUSE_NAME as ppWarehouseName,"
					+ "t.PP_UNIT_PRICE as ppUnitPrice,"
					+ "t.DELETE_FLAG as deleteFlag,"
					+ "t.TOTAL_PRICE as totalPrice,"
					+ "t.SALE_PRICE as salePrice,"
					+ "t.PRODBAR_CODE as prodBarCode, "
					+ "t.COMMODITY_NO as commodityNo, "
					+ "t.RETURN_NUMBER as returnNumber, "
					+ "t.RETURN_STATUS as retunStatus, "
					+ "t.SHELFLIFE as shelfLife, "
					+ "t.MANUFACTURE_TIME as manufactureTime "
					+ "from TBL_REQUISITION_PRODUCT t "
					+ "where t.PO_NO = '"+purchase.getPoNo()+"' "
					+ " and t.PP_PRODUCT_NO = '"+purchase.getProdNo()+"'");
			Query query= getSession().createSQLQuery(sql.toString())
					.addScalar("id",StandardBasicTypes.STRING)
					.addScalar("poNo",StandardBasicTypes.STRING)
					.addScalar("ppName",StandardBasicTypes.STRING)
					.addScalar("productNo",StandardBasicTypes.STRING)
					.addScalar("ppSpecification",StandardBasicTypes.STRING)
					.addScalar("ppUnit",StandardBasicTypes.STRING)
					.addScalar("ppNumber",StandardBasicTypes.INTEGER)
					.addScalar("ppSupplierNo",StandardBasicTypes.STRING)
					.addScalar("ppSupplierName",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseNo",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseName",StandardBasicTypes.STRING)
					.addScalar("ppUnitPrice",StandardBasicTypes.STRING)
					.addScalar("deleteFlag",StandardBasicTypes.INTEGER)
					.addScalar("totalPrice",StandardBasicTypes.STRING)
					.addScalar("salePrice",StandardBasicTypes.STRING)
					.addScalar("prodBarCode",StandardBasicTypes.STRING)
					.addScalar("returnNumber",StandardBasicTypes.INTEGER)
					.addScalar("commodityNo",StandardBasicTypes.STRING)
					.addScalar("retunStatus",StandardBasicTypes.INTEGER)
					.addScalar("shelfLife",StandardBasicTypes.STRING)
					.addScalar("manufactureTime",StandardBasicTypes.STRING);
			
			query.setResultTransformer(Transformers.aliasToBean(PurchaseRequisitionProduct.class));
			List<PurchaseRequisitionProduct> purchaseList = query.list();
			return purchaseList;
		}
		return null;
		
	}



	/**
	 * 获取过期的货品
	 */
	@Override
	public PageFinder<PurchaseRequisitionProduct> purchaseOrderListToExpired(PurchaseRequisitionProduct purchaseRequisitionProduct, PageQuery pageQuery) {
		if(purchaseRequisitionProduct!=null){
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
		    String dateNowStr = sdf.format(new Date());  
			StringBuffer hql = new StringBuffer("select count(*) from TBL_REQUISITION_PRODUCT t  join tbl_purchase_order e on t.PO_NO = e.PO_NO  where ((DATEDIFF(AddDate(t.MANUFACTURE_TIME,t.SHELFLIFE),'"+dateNowStr+"'))) <= 0 and (e.PO_STATUS != 3 OR e.PO_STATUS != 4 OR e.PO_STATUS != 5 OR e.PO_STATUS != - 1)  and t.DELETE_FLAG =1 ");
			StringBuffer sql = new StringBuffer("SELECT "
					+ "t.ID as id,"
					+ "t.PO_NO as poNo,"
					+ "t.PP_NAME as ppName,"
					+ "t.PP_PRODUCT_NO as productNo,"
					+ "t.PP_SPECIFICATION as ppSpecification,"
					+ "t.PP_UNIT as ppUnit,"
					+ "t.PP_NUMBER as ppNumber,"
					+ "t.PP_SUPPLIER_NO as ppSupplierNo,"
					+ "t.PP_SUPPLIER_NAME as ppSupplierName,"
					+ "t.PP_WAREHOUSE_NO as ppWarehouseNo,"
					+ "t.PP_WAREHOUSE_NAME as ppWarehouseName,"
					+ "t.PP_UNIT_PRICE as ppUnitPrice,"
					+ "t.DELETE_FLAG as deleteFlag,"
					+ "t.TOTAL_PRICE as totalPrice,"
					+ "t.SALE_PRICE as salePrice,"
					+ "t.PRODBAR_CODE as prodBarCode,"
					+ "t.COMMODITY_NO as commodityNo,"
					+ "t.RETURN_NUMBER as returnNumber,"
					+ "t.RETURN_STATUS as retunStatus,"
					+ "t.BUYNUM as buyNum,"
					+ "t.SHELFLIFE as shelfLife,"
					+ "t.MANUFACTURE_TIME as manufactureTime "
					+ "from TBL_REQUISITION_PRODUCT t join tbl_purchase_order e on t.PO_NO = e.PO_NO "
					+ "  where ((DATEDIFF(AddDate(t.MANUFACTURE_TIME,t.SHELFLIFE),'"+dateNowStr+"'))) <=0 and (e.PO_STATUS != 3 OR e.PO_STATUS != 4 OR e.PO_STATUS != 5 OR e.PO_STATUS != - 1) "
//					+ "where (DATEDIFF('"+dateNowStr+"',t.SHELFLIFE)) >= 0 "
					+ "and t.DELETE_FLAG =1 ");
			
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPoNo())){
					String aql = " and t.PO_NO = '"+purchaseRequisitionProduct.getPoNo()+"' ";
					sql.append(aql);
					hql.append(aql);
			 } 
			 
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPpName())){
					String bql = " and t.PP_NAME  like '%"+purchaseRequisitionProduct.getPpName()+"%' ";
					sql.append(bql);
					hql.append(bql);
			 } 
			 
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPpSupplierName())){
					String cql = " and t.PP_SUPPLIER_NAME like '%"+purchaseRequisitionProduct.getPpSupplierName()+"%' ";
					sql.append(cql);
					hql.append(cql);
			 } 
			Query query= getSession().createSQLQuery(sql.toString())
					.addScalar("id",StandardBasicTypes.STRING)
					.addScalar("poNo",StandardBasicTypes.STRING)
					.addScalar("ppName",StandardBasicTypes.STRING)
					.addScalar("productNo",StandardBasicTypes.STRING)
					.addScalar("ppSpecification",StandardBasicTypes.STRING)
					.addScalar("ppUnit",StandardBasicTypes.STRING)
					.addScalar("ppNumber",StandardBasicTypes.INTEGER)
					.addScalar("ppSupplierNo",StandardBasicTypes.STRING)
					.addScalar("ppSupplierName",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseNo",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseName",StandardBasicTypes.STRING)
					.addScalar("ppUnitPrice",StandardBasicTypes.STRING)
					.addScalar("deleteFlag",StandardBasicTypes.INTEGER)
					.addScalar("totalPrice",StandardBasicTypes.STRING)
					.addScalar("salePrice",StandardBasicTypes.STRING)
					.addScalar("prodBarCode",StandardBasicTypes.STRING)
					.addScalar("commodityNo",StandardBasicTypes.STRING)
					.addScalar("returnNumber",StandardBasicTypes.INTEGER)
					.addScalar("retunStatus",StandardBasicTypes.INTEGER)
					.addScalar("buyNum",StandardBasicTypes.STRING)
					.addScalar("shelfLife",StandardBasicTypes.STRING)
					.addScalar("manufactureTime",StandardBasicTypes.STRING);
			BigInteger count = (BigInteger) this.createSQLQuery(hql.toString()).list().get(0);
			List list = getSession().createSQLQuery(sql.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.setFirstResult((pageQuery.getPage() - 1) * pageQuery.getPageSize())
					.setMaxResults(pageQuery.getPageSize()).list();
			PageFinder page = new PageFinder(pageQuery.getPage(), pageQuery.getPageSize(), count.intValue(), list);
			return page;
		}else{
			return null;
		}
	}
	
	
	
	/**
	 * 获取库存报警的货品
	 */
	@Override
	public PageFinder<PurchaseRequisitionProduct> purchaseOrderListToInventoryAlarm(PurchaseRequisitionProduct purchaseRequisitionProduct, PageQuery pageQuery) {
		if(purchaseRequisitionProduct!=null){
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
		    String dateNowStr = sdf.format(new Date());  
		    StringBuffer hql = new  StringBuffer("select count(*) from TBL_REQUISITION_PRODUCT t  join tbl_purchase_order e on t.PO_NO = e.PO_NO  where ((DATEDIFF(AddDate(t.MANUFACTURE_TIME,t.SHELFLIFE),'"+dateNowStr+"'))) < 30 and (e.PO_STATUS != 3 OR e.PO_STATUS != 4 OR e.PO_STATUS != 5 OR e.PO_STATUS != - 1)  and t.DELETE_FLAG =1 ");
			StringBuffer sql = new  StringBuffer("SELECT "
					+ "t.ID as id,"
					+ "t.PO_NO as poNo,"
					+ "t.PP_NAME as ppName,"
					+ "t.PP_PRODUCT_NO as productNo,"
					+ "t.PP_SPECIFICATION as ppSpecification,"
					+ "t.PP_UNIT as ppUnit,"
					+ "t.PP_NUMBER as ppNumber,"
					+ "t.PP_SUPPLIER_NO as ppSupplierNo,"
					+ "t.PP_SUPPLIER_NAME as ppSupplierName,"
					+ "t.PP_WAREHOUSE_NO as ppWarehouseNo,"
					+ "t.PP_WAREHOUSE_NAME as ppWarehouseName,"
					+ "t.PP_UNIT_PRICE as ppUnitPrice,"
					+ "t.DELETE_FLAG as deleteFlag,"
					+ "t.TOTAL_PRICE as totalPrice,"
					+ "t.SALE_PRICE as salePrice,"
					+ "t.PRODBAR_CODE as prodBarCode,"
					+ "t.COMMODITY_NO as commodityNo,"
					+ "t.RETURN_NUMBER as returnNumber,"
					+ "t.RETURN_STATUS as retunStatus,"
					+ "t.BUYNUM as buyNum,"
					+ "t.SHELFLIFE as shelfLife,"
					+ "t.MANUFACTURE_TIME as manufactureTime "
					+ "from TBL_REQUISITION_PRODUCT t "
					+ "join tbl_purchase_order e on t.PO_NO = e.PO_NO "
					+ "where  ((DATEDIFF(AddDate(t.MANUFACTURE_TIME,t.SHELFLIFE),'"+dateNowStr+"'))) < 30 and (e.PO_STATUS != 3 OR e.PO_STATUS != 4 OR e.PO_STATUS != 5 OR e.PO_STATUS != - 1)");
//					+ "where (DATEDIFF('"+dateNowStr+"',t.SHELFLIFE)) < 30 ");
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPoNo())){
					String aql = " and t.PO_NO = '"+purchaseRequisitionProduct.getPoNo()+"' ";
					sql.append(aql);
					hql.append(aql);
			 } 
			 
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPpName())){
					String bql = " and t.PP_NAME  like '%"+purchaseRequisitionProduct.getPpName()+"%' ";
					sql.append(bql);
					hql.append(bql);
			 } 
			 
			 if(!StringUtil.isBlank(purchaseRequisitionProduct.getPpSupplierName())){
					String cql = " and t.PP_SUPPLIER_NAME like '%"+purchaseRequisitionProduct.getPpSupplierName()+"%' ";
					sql.append(cql);
					hql.append(cql);
			 } 
			 
			Query query= getSession().createSQLQuery(sql.toString())
					.addScalar("id",StandardBasicTypes.STRING)
					.addScalar("poNo",StandardBasicTypes.STRING)
					.addScalar("ppName",StandardBasicTypes.STRING)
					.addScalar("productNo",StandardBasicTypes.STRING)
					.addScalar("ppSpecification",StandardBasicTypes.STRING)
					.addScalar("ppUnit",StandardBasicTypes.STRING)
					.addScalar("ppNumber",StandardBasicTypes.INTEGER)
					.addScalar("ppSupplierNo",StandardBasicTypes.STRING)
					.addScalar("ppSupplierName",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseNo",StandardBasicTypes.STRING)
					.addScalar("ppWarehouseName",StandardBasicTypes.STRING)
					.addScalar("ppUnitPrice",StandardBasicTypes.STRING)
					.addScalar("deleteFlag",StandardBasicTypes.INTEGER)
					.addScalar("totalPrice",StandardBasicTypes.STRING)
					.addScalar("salePrice",StandardBasicTypes.STRING)
					.addScalar("prodBarCode",StandardBasicTypes.STRING)
					.addScalar("commodityNo",StandardBasicTypes.STRING)
					.addScalar("returnNumber",StandardBasicTypes.INTEGER)
					.addScalar("retunStatus",StandardBasicTypes.INTEGER)
					.addScalar("buyNum",StandardBasicTypes.STRING)
					.addScalar("shelfLife",StandardBasicTypes.STRING)
					.addScalar("manufactureTime",StandardBasicTypes.STRING);
			BigInteger count = (BigInteger) this.createSQLQuery(hql.toString()).list().get(0);
			List list = getSession().createSQLQuery(sql.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.setFirstResult((pageQuery.getPage() - 1) * pageQuery.getPageSize())
					.setMaxResults(pageQuery.getPageSize()).list();
			PageFinder page = new PageFinder(pageQuery.getPage(), pageQuery.getPageSize(), count.intValue(), list);
			return page;
		}else{
			return null;
		}
		
	}
	
	
	
	
}
